package com.bdy.lm.browser.dao;

import java.io.ByteArrayInputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.junit.Test;

import cn.itcast.jdbc.JdbcUtils;
import cn.itcast.jdbc.TxQueryRunner;

import com.bdy.lm.browser.domain.Driver;
import com.bdy.lm.utils.ImageUtil;

public class DriverDao {

	private Connection con = null;// 定义引用
	private PreparedStatement ps = null;
	private ResultSet rs = null;
	private QueryRunner qr = new TxQueryRunner();

	public Driver queryDriverById(String id) {
		try {
			String sql = "SELECT * FROM driver_tb WHERE id=?";
			return qr.query(sql, new BeanHandler<Driver>(Driver.class), id);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public List<Driver> queryDriver(String queryMode, String queryContent)
			throws SQLException {
		try {
			String sql = "SELECT * FROM driver_tb WHERE " + queryMode + " = ?";//driverName,sex,id,employId,employCardAgent,timeBeginEnd,tel,companyId,education,serviceGrade,ICID,driverPhoto
			Object[] params = { queryContent };
			List<Driver> driver = qr.query(sql, new BeanListHandler<Driver>(Driver.class), params);
			return driver;
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}

	public boolean addDriver(Driver driver) throws SQLException, IOException {
		InputStream in = new ByteArrayInputStream(driver.getDriverPhoto());
		try {
			String sql = "INSERT INTO driver_tb VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
			Object[] params = { driver.getDriverName(), driver.getSex(),
					driver.getId(), driver.getEmployId(),
					driver.getEmployCardAgent(), driver.getTimeBeginEnd(),
					driver.getTel(), driver.getCompanyId(),
					driver.getEducation(), driver.getServiceGrade(),
					driver.getICID(), in };
			if (qr.update(sql, params) > 0) {
				return true;
			} else {
				return false;// 该情况没有处理
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			in.close();
		}
	}
	public int modifyDriver(Driver driver) throws SQLException, IOException {
		InputStream in = new ByteArrayInputStream(driver.getDriverPhoto());
		try {
			System.out.println("开始");
			String sql = "UPDATE driver_tb SET driverName=?,sex=?,employId=?,employCardAgent=?,timeBeginEnd=?,tel=?,companyId=?,education=?,serviceGrade=?,ICID=?,driverPhoto=? WHERE id=?";
			Object[] params = { driver.getDriverName(), driver.getSex(), driver.getEmployId(),
					driver.getEmployCardAgent(), driver.getTimeBeginEnd(),driver.getTel(),
					driver.getCompanyId(), driver.getEducation(), driver.getServiceGrade(),
					driver.getICID(),in, driver.getId() };
			return qr.update(sql, params);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			in.close();
		}
	}
	public boolean deleteDriver(List<String> id) throws SQLException {

		try {
			String sql = "DELETE FROM driver_tb WHERE id=?";
			int sum = 0;
			for (int i = 0; i < id.size(); i++) {
				Object[] params = { id.get(i) };
				sum += qr.update(sql, params);
			}
			if (sum == id.size()) {
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		}
	}
	
	//测试用
	public boolean addDriverPhoto(String ICID) throws SQLException{
		try {
			FileInputStream in = ImageUtil.readImage("C:\\Users\\Administrator\\Desktop\\5157ba4997331_200x200_3.jpg");
			String sql = "UPDATE driver_tb SET driverPhoto=? WHERE ICID=?";
			System.out.println(sql);
			con = JdbcUtils.getConnection();
			ps = con.prepareStatement(sql);
			ps.setBinaryStream(1, in, in.available());
			ps.setString(2, ICID);
			if (ps.executeUpdate() > 0) {
				return true;
			} else {
				return false;
			}
		} catch (Exception e) {
			throw new RuntimeException(e);
		} finally {
			 if (con != null)
			 con.close();
			 if (ps != null)
			 con.close();
		}
	}
	
	public byte[] queryDriverPhoto(String ICID) throws SQLException{
		byte[] driverPhoto = null;
        try {
            String sql = "SELECT driverPhoto FROM driver_tb WHERE ICID=?";
            con = JdbcUtils.getConnection();
			ps = con.prepareStatement(sql);
            ps.setString(1, ICID);
            rs = ps.executeQuery();
            while (rs.next()) {
            	InputStream in = rs.getBinaryStream("driverPhoto");
            	if (in != null) {
                	driverPhoto = new byte[in.available()];
                    in.read(driverPhoto);
				}
                //ImageUtil.readBin2Image(in, targetPath);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
			 if (rs != null)
			 rs.close();
			 if (con != null)
			 con.close();
			 if (ps != null)
			 con.close();
        }
		return driverPhoto;
	}

}
